
from typing import List
from models.task import DbTask
from database.database import Database
from models.query import TaskQuery

class TaskTable(Database):

    def __init__(self):
        self.table_name = 'tasks'

    def insert(self, data : DbTask):
        sql = self.get_insert_sql_from_obj(data, self.table_name)
        self.execute(sql)
        return int(self.cursor.lastrowid)

    def update(self, data : DbTask): 
        sql = self.get_update_sql_from_obj(data.id, data, self.table_name)
        self.execute(sql)

    def delete(self, id : int):
        sql = f'update {self.table_name} set `erased` = 1 where `id` = {id}'
        self.execute(sql)

    def getTask(self, id : int) -> DbTask :
        sql = f'select * from {self.table_name} where `id` = %s'
        self.execute(sql,(id))
        result = self.cursor.fetchone()
        if not result:
            return None
        return DbTask().load(result)

    def query(self, query : TaskQuery, total : bool):
        
        args = []

        sql = f'select {"*" if not total else "count(`id`) as total"} from {self.table_name} where `erased` = 0'

        if query.id >= 0:
            sql += f' and `id` = %s'
            args.append(query.id)
            
        if query.user_id >= 0:
            sql += f' and `user_id` = %s'
            args.append(query.user_id)
            
        if query.status >= 0:
            sql += f' and `status` = %s'
            args.append(query.status)

        if query.search:
            sql += f''' and (`name` like %s)''' 
            args.append(f'%{query.search}%')

        sql += f' order by id desc'

        if total:
            self.execute(sql, args)
            record = self.cursor.fetchone()
            return record['total']
        else:
            sql += f' limit {(query.page - 1) * query.limit},{query.limit}'
            self.execute(sql, args)
            records = self.cursor.fetchall()
            results : List[DbTask] = []
            for record in records:
                results.append(DbTask().load(record))
            return results
